﻿


--获取一个员工档案
CREATE proc [dbo].[proc_Employee_GetItem]
(
	@id int,
	@username nvarchar (50)
)
as
	if(@id >0 and @username='')
	  begin
		select 
			id,
			UserName,
			EName,
			ENumber,
			ETempNumber,
			Sex,
			IsNull(CompanyId,0) as CompanyId,
			(select CompanyName from Company where Id = CompanyId) as CompanyName,
			DepartmentId,
			(select DName from Department  where Id = DepartmentId) as DepartmentName,
			IsNull(PostCategory,0) as PostCategory,
			isnull((select Title from Post_Category where Post_Category.Id = PostCategory),'...') as PostCategoryName,
			PostId,
			(select PostName from Post where Id = Employee.PostId) as PostName,
			GradeId,
			(select Title from Grade where Id = GradeId) as GradeName,
			State,
			(select Title from Employee_State where Id = State) as StateName,
			RuzhiDate,
			LizhiDate,
			Birthday,
			Rtx,
			OfficePhone,
			HomePhone,
			MobilePhone,
			Email,
			IDcard,
			Address,
			Education,
			(select Title from Employee_Education where Id = Education) as EducationName,
			HeadPic,
			WageNumber,
			isnull(WageCardNo,'') as WageCardNo,
			WorkYM,
			Marriage,
			PoliticsStatus,
			Pluralist,
			Cuenta,
			UpName,
			UpTime,
			(select top 1  auditperson from employee_entry where employee_entry.eid=employee.id  order by id desc) as AuditPerson,
			(select PostName from post where id=postid) as PostName,
			(select title from Grade where id=gradeid) as GradeName,
			 ZzDate,
			ProbationFrom,
			ProbationTo,
			(select (datediff(day,RuzhiDate,getdate())/30)) as WorkAge,
			ZhiWu,
			IsTxlz,
			IsCore,
			Pid,
			(Select p.Title From Province p  where p.Id=Employee.Pid) as pname,
			Cid,
			(Select c.Title From city c  where c.Id=Employee.cid) as cname,
			IsInsured,
			isnull(OldEName,'') AS OldEName,
			isnull(Nation,'') AS Nation,
			isnull(ShenGao,0) AS ShenGao,
			isnull(BloodType,'') AS BloodType,
			isnull(YXZY,'') AS YXZY,
			isnull(SkillName,'') AS SkillName,
			isnull(NativePlace,'') AS NativePlace,
			isnull(QQ,'') AS QQ,
			isnull(ForeignLanguage,'') AS ForeignLanguage,
			isnull(DriveType,'') AS DriveType,
			isnull(Hobby,'') AS Hobby,
			ISNULL(XZJB,'') AS XZJB,GID,
						convert(varchar(7),(select top 1 enddate from (select (convert (datetime,enddate+'-01')) enddate from Employee_EducationResume where Employee_EducationResume.eid=Employee.id ) as b order by enddate desc ),120) as bysj,
           isnull(ContractEndDate,'1900-01-01') as ContractEndDate, --合同到期日期
		   isnull(ZhiWuId,'0') as ZhiWuId --职务ID
		   --,Isnull((Select Title from Employee_ZhiWu where Id=Employee.ZhiWuId),'') as ZhiWuTitle --职务名称
		from
			Employee
		where 
			id = @id
	
	  end
	else if(@id=0 and @username!='')
		begin
			select 
			id,
			UserName,
			EName,
			ENumber,
			ETempNumber,
			Sex,
			CompanyId,
			(select CompanyName from Company where Id = CompanyId) as CompanyName,
			DepartmentId,
			(select DName from Department  where Id = DepartmentId) as DepartmentName,
			IsNULL(PostCategory,0) AS PostCategory,
			isnull((select Title from Post_Category where Post_Category.Id = PostCategory),'...') as PostCategoryName,
			PostId,
			(select PostName from Post where Id = Employee.PostId) as PostName,
			GradeId,
			(select Title from Grade where Id = GradeId) as GradeName,
			State,
			(select Title from Employee_State where Id = State) as StateName,
			RuzhiDate,
			LizhiDate,
			Birthday,
			Rtx,
			OfficePhone,
			HomePhone,
			MobilePhone,
			Email,
			IDcard,
			Address,
			Education,
			(select Title from Employee_Education where Id = Education) as EducationName,
			HeadPic,
			WageNumber,
			isnull(WageCardNo,'') as WageCardNo,
			WorkYM,
			Marriage,
			PoliticsStatus,
			Pluralist,
			Cuenta,
			UpName,
			UpTime,
			(select top 1  auditperson from employee_entry where employee_entry.eid=employee.id  order by id desc) as AuditPerson,
			(select PostName from post where id=postid) as PostName,
			(select title from Grade where id=gradeid) as GradeName,
			 ZzDate,
			ProbationFrom,
			ProbationTo,
			(select (datediff(day,RuzhiDate,getdate())/30)) as WorkAge,
			ZhiWu,
			IsTxlz,
			IsCore,
			Pid,
			(Select p.Title From Province p  where p.Id=Employee.Pid) as pname,
			Cid,
			(Select c.Title From city c  where c.Id=Employee.cid) as cname,
			IsInsured,
			isnull(OldEName,'...') AS OldEName,
			isnull(Nation,'...') AS Nation,
			isnull(ShenGao,0) AS ShenGao,
			isnull(BloodType,'...') AS BloodType,
			isnull(YXZY,'...') AS YXZY,
			isnull(SkillName,'...') AS SkillName,
			isnull(NativePlace,'...') AS NativePlace,
			isnull(QQ,'...') AS QQ,
			isnull(ForeignLanguage,'...') AS ForeignLanguage,
			isnull(DriveType,'...') AS DriveType,
			isnull(Hobby,'...') AS Hobby,
			ISNULL(XZJB,'') AS XZJB,
			GID,
			convert(varchar(7),(select top 1 enddate from (select (convert (datetime,enddate+'-01')) enddate from Employee_EducationResume where Employee_EducationResume.eid=Employee.id ) as b order by enddate desc ),120) as bysj

		from
			Employee
		where 
			username=@username
			
		end


